﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_client_details
{
    public DAL_tbl_client_details()
    {

    }



    public List<tbl_client_details> select(String USER_CODE)
    {
        List<tbl_client_details> list = new List<tbl_client_details>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select USER_CODE,COMPANY_NAME,COMPANY_ADDRESS,Country,STATE,CITY,POSTAL_CODE,PRIMARY_TELEPHONE,SECONDARY_TELEPHONE,PRIMARY_MOBILE,SECONDARY_MOBILE,Email,	Agreement_Document, Agreement_Document_GUID,	Link_To_ISF, Link_To_ISF_GUID,	FAX,	LinkedIn_ID,	Skype_ID,	VAT_Tax_ID,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,Active from tbl_client_details where USER_CODE=@USER_CODE";
        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_client_details()
            {
                USER_CODE = dr["USER_CODE"].ToString(),
                COMPANY_NAME = dr["COMPANY_NAME"].ToString(),
                COMPANY_ADDRESS = dr["COMPANY_ADDRESS"].ToString(),
                Country = dr["Country"].ToString(),
                STATE = dr["STATE"].ToString(),
                CITY = dr["CITY"].ToString(),
                POSTAL_CODE = dr["POSTAL_CODE"].ToString(),
                PRIMARY_TELEPHONE = dr["PRIMARY_TELEPHONE"].ToString(),
                SECONDARY_TELEPHONE = dr["SECONDARY_TELEPHONE"].ToString(),
                PRIMARY_MOBILE = dr["PRIMARY_MOBILE"].ToString(),
                SECONDARY_MOBILE = dr["SECONDARY_MOBILE"].ToString(),

                Email = dr["Email"].ToString(),
                Agreement_Document = dr["Agreement_Document"].ToString(),
                Agreement_Document_GUID = dr["Agreement_Document_GUID"].ToString(),
                Link_To_ISF = dr["Link_To_ISF"].ToString(),
                Link_To_ISF_GUID = dr["Link_To_ISF_GUID"].ToString(),
                FAX = dr["FAX"].ToString(),
                LinkedIn_ID = dr["LinkedIn_ID"].ToString(),
                Skype_ID = dr["Skype_ID"].ToString(),
                VAT_Tax_ID = dr["VAT_Tax_ID"].ToString(),

                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError(),
                Active = Convert.ToBoolean(dr["Active"])
            });
        }

        return list;
    }



    public List<tbl_client_details> selectAll()
    {
        List<tbl_client_details> list = new List<tbl_client_details>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select USER_CODE,COMPANY_NAME,COMPANY_ADDRESS,Country,STATE,CITY,POSTAL_CODE,PRIMARY_TELEPHONE,SECONDARY_TELEPHONE,PRIMARY_MOBILE,SECONDARY_MOBILE,Email,	Agreement_Document,Agreement_Document_GUID,	Link_To_ISF,Link_To_ISF_GUID,	FAX,	LinkedIn_ID,	Skype_ID,	VAT_Tax_ID,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,Active from tbl_client_details Order By USER_CODE";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_client_details()
            {
                USER_CODE = dr["USER_CODE"].ToString(),
                COMPANY_NAME = dr["COMPANY_NAME"].ToString(),
                COMPANY_ADDRESS = dr["COMPANY_ADDRESS"].ToString(),
                Country = dr["Country"].ToString(),
                STATE = dr["STATE"].ToString(),
                CITY = dr["CITY"].ToString(),
                POSTAL_CODE = dr["POSTAL_CODE"].ToString(),
                PRIMARY_TELEPHONE = dr["PRIMARY_TELEPHONE"].ToString(),
                SECONDARY_TELEPHONE = dr["SECONDARY_TELEPHONE"].ToString(),
                PRIMARY_MOBILE = dr["PRIMARY_MOBILE"].ToString(),
                SECONDARY_MOBILE = dr["SECONDARY_MOBILE"].ToString(),

                Email = dr["Email"].ToString(),
                Agreement_Document = dr["Agreement_Document"].ToString(),
                Agreement_Document_GUID = dr["Agreement_Document_GUID"].ToString(),
                Link_To_ISF = dr["Link_To_ISF"].ToString(),
                Link_To_ISF_GUID = dr["Link_To_ISF_GUID"].ToString(),
                FAX = dr["FAX"].ToString(),
                LinkedIn_ID = dr["LinkedIn_ID"].ToString(),
                Skype_ID = dr["Skype_ID"].ToString(),
                VAT_Tax_ID = dr["VAT_Tax_ID"].ToString(),

                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError(),
                Active = Convert.ToBoolean(dr["Active"])
            });
        }

        return list;
    }



    public int insert(String USER_CODE, String COMPANY_NAME, String COMPANY_ADDRESS, String Country, String STATE, String CITY, String POSTAL_CODE, String PRIMARY_TELEPHONE, String SECONDARY_TELEPHONE, String PRIMARY_MOBILE, String SECONDARY_MOBILE, String Email, String Agreement_Document, String Agreement_Document_GUID, String Link_To_ISF, String Link_To_ISF_GUID, String FAX, String LinkedIn_ID, String Skype_ID, String VAT_Tax_ID, String CREATED_BY, DateTime CREATED_DATE, bool Active)
    {
        int i = 0;
        string sql = @"Insert into tbl_client_details(USER_CODE, COMPANY_NAME, COMPANY_ADDRESS, Country, STATE, CITY, POSTAL_CODE, PRIMARY_TELEPHONE, SECONDARY_TELEPHONE, PRIMARY_MOBILE, SECONDARY_MOBILE,Email,	Agreement_Document, Agreement_Document_GUID,	Link_To_ISF, Link_To_ISF_GUID,	FAX,	LinkedIn_ID,	Skype_ID,	VAT_Tax_ID, CREATED_BY, CREATED_DATE, Active)
                        values(@USER_CODE, @COMPANY_NAME, @COMPANY_ADDRESS, @Country, @STATE, @CITY, @POSTAL_CODE, @PRIMARY_TELEPHONE, @SECONDARY_TELEPHONE, @PRIMARY_MOBILE, @SECONDARY_MOBILE,@Email,	@Agreement_Document, @Agreement_Document_GUID,	@Link_To_ISF, @Link_To_ISF_GUID,	@FAX,	@LinkedIn_ID,	@Skype_ID,	@VAT_Tax_ID, @CREATED_BY, @CREATED_DATE, @Active)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@COMPANY_NAME", COMPANY_NAME);
        cmd.Parameters.AddWithValue("@COMPANY_ADDRESS", COMPANY_ADDRESS);
        cmd.Parameters.AddWithValue("@Country", Country);
        cmd.Parameters.AddWithValue("@STATE", STATE);
        cmd.Parameters.AddWithValue("@CITY", CITY);
        cmd.Parameters.AddWithValue("@POSTAL_CODE", POSTAL_CODE);
        cmd.Parameters.AddWithValue("@PRIMARY_TELEPHONE", PRIMARY_TELEPHONE);
        cmd.Parameters.AddWithValue("@SECONDARY_TELEPHONE", SECONDARY_TELEPHONE);
        cmd.Parameters.AddWithValue("@PRIMARY_MOBILE", PRIMARY_MOBILE);
        cmd.Parameters.AddWithValue("@SECONDARY_MOBILE", SECONDARY_MOBILE);

        cmd.Parameters.AddWithValue("@Email", Email);
        cmd.Parameters.AddWithValue("@Agreement_Document", Agreement_Document);
        cmd.Parameters.AddWithValue("@Agreement_Document_GUID", Agreement_Document_GUID);
        cmd.Parameters.AddWithValue("@Link_To_ISF", Link_To_ISF);
        cmd.Parameters.AddWithValue("@Link_To_ISF_GUID", Link_To_ISF_GUID);
        cmd.Parameters.AddWithValue("@FAX", FAX);
        cmd.Parameters.AddWithValue("@LinkedIn_ID", LinkedIn_ID);
        cmd.Parameters.AddWithValue("@Skype_ID", Skype_ID);
        cmd.Parameters.AddWithValue("@VAT_Tax_ID", VAT_Tax_ID);

        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);
        cmd.Parameters.AddWithValue("@Active", Active);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(String USER_CODE, String COMPANY_NAME, String COMPANY_ADDRESS, String Country, String STATE, String CITY, String POSTAL_CODE, String PRIMARY_TELEPHONE, String SECONDARY_TELEPHONE, String PRIMARY_MOBILE, String SECONDARY_MOBILE, String Email, String Agreement_Document, String Agreement_Document_GUID, String Link_To_ISF, String Link_To_ISF_GUID, String FAX, String LinkedIn_ID, String Skype_ID, String VAT_Tax_ID, String UPDATED_BY, DateTime UPDATED_DATE, bool Active)
    {
        int i = 0;
        string sql = @"Update tbl_client_details SET COMPANY_NAME=@COMPANY_NAME, COMPANY_ADDRESS=@COMPANY_ADDRESS, Country=@Country, STATE=@STATE, CITY=@CITY, POSTAL_CODE=@POSTAL_CODE, PRIMARY_TELEPHONE=@PRIMARY_TELEPHONE, SECONDARY_TELEPHONE=@SECONDARY_TELEPHONE, PRIMARY_MOBILE=@PRIMARY_MOBILE, SECONDARY_MOBILE=@SECONDARY_MOBILE,Email=@Email,	Agreement_Document=@Agreement_Document, Agreement_Document_GUID=@Agreement_Document_GUID,	Link_To_ISF=@Link_To_ISF, Link_To_ISF_GUID=@Link_To_ISF_GUID,	FAX=@FAX,	LinkedIn_ID=@LinkedIn_ID,	Skype_ID=@Skype_ID,	VAT_Tax_ID=@VAT_Tax_ID, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE, Active=@Active WHERE USER_CODE=@USER_CODE";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@COMPANY_NAME", COMPANY_NAME);
        cmd.Parameters.AddWithValue("@COMPANY_ADDRESS", COMPANY_ADDRESS);
        cmd.Parameters.AddWithValue("@Country", Country);
        cmd.Parameters.AddWithValue("@STATE", STATE);
        cmd.Parameters.AddWithValue("@CITY", CITY);
        cmd.Parameters.AddWithValue("@POSTAL_CODE", POSTAL_CODE);
        cmd.Parameters.AddWithValue("@PRIMARY_TELEPHONE", PRIMARY_TELEPHONE);
        cmd.Parameters.AddWithValue("@SECONDARY_TELEPHONE", SECONDARY_TELEPHONE);
        cmd.Parameters.AddWithValue("@PRIMARY_MOBILE", PRIMARY_MOBILE);
        cmd.Parameters.AddWithValue("@SECONDARY_MOBILE", SECONDARY_MOBILE);

        cmd.Parameters.AddWithValue("@Email", Email);
        cmd.Parameters.AddWithValue("@Agreement_Document", Agreement_Document);
        cmd.Parameters.AddWithValue("@Agreement_Document_GUID", Agreement_Document_GUID);
        cmd.Parameters.AddWithValue("@Link_To_ISF", Link_To_ISF);
        cmd.Parameters.AddWithValue("@Link_To_ISF_GUID", Link_To_ISF_GUID);
        cmd.Parameters.AddWithValue("@FAX", FAX);
        cmd.Parameters.AddWithValue("@LinkedIn_ID", LinkedIn_ID);
        cmd.Parameters.AddWithValue("@Skype_ID", Skype_ID);
        cmd.Parameters.AddWithValue("@VAT_Tax_ID", VAT_Tax_ID);

        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);
        cmd.Parameters.AddWithValue("@Active", Active);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(String USER_CODE)
    {
        int i = 0;
        string sql = "Delete FROM tbl_client_details WHERE USER_CODE=@USER_CODE";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



}